﻿using MongoDB.Driver;
using Sustainalytics.ClientDW.Entities;
using System;
using System.Collections.Generic;
using System.Configuration.Abstractions;
using System.Linq;
using Npgsql;
using System.Net;
using System.Net.Http;
using System.Net.Security;
using System.Threading.Tasks;

namespace Sustainalytics.Companies
{
    public class CompanyMatchingLogic: ICompanyMatchingLogic
    {
        private readonly IMongoCollection<CorporateData> _corporateDataCollection;
        private const string CoverageString = "coverage";

        public CompanyMatchingLogic(IMongoCollection<CorporateData> corporateDataCollection)
        {
            _corporateDataCollection = corporateDataCollection;
        }

        public List<Company> MatchCompanies(int skip, int take, string term, bool searchInNames = true)
        {
            if (term == null)
            {
                throw new ArgumentNullException("term", "The term can not be null");
            }

            var list = new List<Company>();
            var command = searchInNames ? "select * from api.company_search(@identifier)" : "select * from api.company_search_identifiers(@identifier)";

            using (var conn = new NpgsqlConnection(GetConnectionString()))
            {
                using (var cmd = new NpgsqlCommand(command, conn))
                {
                    try
                    {
                        conn.Open();
                        CompaniesSerilog.Log.OpenConnectionSucces();
                        cmd.Parameters.AddWithValue("identifier", NpgsqlTypes.NpgsqlDbType.Varchar, term);
                        using (var reader = cmd.ExecuteReader())
                        {
                            try
                            {
                                CompaniesSerilog.Log.ReadCompaniesSucces();
                                while (reader.Read())
                                {
                                    list.Add(new Company
                                    {
                                        CompanyId = reader.GetStringValueOrDefault(8) == CoverageString ? reader.GetInt32(6) : reader.GetInt32(0),
                                        CompanyName = reader.GetStringValueOrDefault(8) == CoverageString ? reader.GetStringValueOrDefault(7) : reader.GetStringValueOrDefault(1),
                                    });
                                }
                            }
                            catch (Exception ex)
                            {
                                CompaniesSerilog.Log.ReadCompaniesError(ex);
                            }
                        }
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        if (conn.State == System.Data.ConnectionState.Open)
                            conn.Close();
                        CompaniesSerilog.Log.OpenConnectionError(ex);

                    }
                }
            }

            var corporateDataCompanies = _corporateDataCollection
                .Find(Builders<CorporateData>.Filter.In(x => x.CapitalIqId, list.Select(y => y.CompanyId)))
                .Project<CorporateData>(Builders<CorporateData>.Projection
                    .Include(x => x.CapitalIqId)
                    .Include(x => x.CompanyName))
                    .ToList()
                    .Select(x => new Company
                    {
                        CompanyId = x.CapitalIqId,
                        CompanyName = x.CompanyName
                    });
            if (take > 0) return corporateDataCompanies.Skip(skip).Take(take).ToList();
            else return corporateDataCompanies.ToList();
        }

        private string GetConnectionString()
        {
            var connectionStringBuilder = new NpgsqlConnectionStringBuilder();

            connectionStringBuilder.Database = ConfigurationManager.Instance.AppSettings["DatabaseName"] ?? "postgres";
            connectionStringBuilder.Host = ConfigurationManager.Instance.AppSettings["ServerName"] ?? "live-digital-platforms.coejoveatmrr.eu-west-1.rds.amazonaws.com";
            connectionStringBuilder.Port = int.TryParse(ConfigurationManager.Instance.AppSettings["ServerPort"], out int port) ? port : 5432;
            connectionStringBuilder.Username = ConfigurationManager.Instance.AppSettings["DbUsername"] ?? "liveuser";
            connectionStringBuilder.Password = ConfigurationManager.Instance.AppSettings["DbPassword"] ?? "92AszJhQBsvJnedz";

            return connectionStringBuilder.ConnectionString;
        }

        public IList<int> FindCapitalIdIdsForCompanies(ICollection<Guid> ids)
        {
            return _corporateDataCollection
                    .Find(Builders<CorporateData>.Filter.In(x => x.Id, ids))
                    .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.CapitalIqId))
                    .ToList()
                    .Select(x => x.CapitalIqId)
                    .ToList();
        }

        public int FindCapitalIqIdForGuid(Guid id)
        {
            var cdata = _corporateDataCollection.Find(x => x.Id == id)
                .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.CapitalIqId))
                .FirstOrDefault();

            if (cdata == null)
                throw new CompanyMatchNotFoundException(string.Format("For term {0} there no existing match in CorporateData collection", id));

            return cdata.CapitalIqId;
        }

        public string FindSecurityIdForCompanyId(int companyId)
        {
            var cdata = _corporateDataCollection
                .Find(x => x.CapitalIqId == companyId)
                .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x=> x.MainIdentifiers))
                .FirstOrDefault();

            if (cdata == null)
                throw new CompanyMatchNotFoundException(string.Format("For term {0} there no existing match in CorporateData collection", companyId));

            return cdata.MainIdentifiers.SecurityId;
        }

        public Guid FindGuidForOneCompany(Company company)
        {
            var cdata = _corporateDataCollection
               .Find(x => x.CapitalIqId == company.CompanyId)
               .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.MainIdentifiers))
               .FirstOrDefault();

            if (cdata == null)
                throw new CompanyMatchNotFoundException(string.Format("For term {0} there no existing match in CorporateData collection", company.CompanyId));

            return cdata.Id;
        }

        public string FindCompanyNameFriendlyURLForId(int capitalIqId)
        {
            var cdata = _corporateDataCollection
                .Find(x => x.CapitalIqId == capitalIqId)
                .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.CompanyNameUrlFriendly))
                .FirstOrDefault();

            if (cdata == null)
                throw new CompanyMatchNotFoundException(string.Format("For term {0} there no existing match in CorporateData collection", capitalIqId));

            return cdata.CompanyNameUrlFriendly;
        }

        public int FindOneCompanyByIdentifier(IdentifierType type, string param)
        {
            var searchData = param ?? string.Empty;
            int? companyId = null;

            if (type == IdentifierType.CompanyName || type == IdentifierType.CompanyNameLowercased)
            {
                companyId = _corporateDataCollection
                    .Find(x => x.CompanyNameLowercase == searchData.ToLowerInvariant())
                    .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x=> x.CapitalIqId))
                    .FirstOrDefault()?
                    .CapitalIqId;
            }
            if (type == IdentifierType.CompanyNameUrlFriendly)
            {
                companyId = _corporateDataCollection
                    .Find(x => x.CompanyNameUrlFriendly == searchData)
                    .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.CapitalIqId))
                    .FirstOrDefault()?
                    .CapitalIqId;
            }

            if (!companyId.HasValue)
                throw new CompanyMatchNotFoundException(string.Format("For term {0} there no existing match in CorporateData collection", param));

            return companyId.Value;
        }

        public IList<Company> GetCompanySearchByCompanyId(int capitalIqId)
        {
            if (capitalIqId == 0)
                throw new ArgumentException("capitalIqId)");

            var cdata = _corporateDataCollection
                .Find(x => x.CapitalIqId == capitalIqId)
                .Project<CorporateData>(Builders<CorporateData>.Projection
                    .Include(x => x.CapitalIqId)
                    .Include(x => x.CompanyName)
                    .Include(x => x.MainTicker))
                .FirstOrDefault();

            if (cdata == null)
                throw new CompanyMatchNotFoundException($"For capitalIqId {capitalIqId} there no existing match in CompanySearch collection");

            return new List<Company>() {
                new Company
                {
                    CompanyId = cdata.CapitalIqId,
                    CompanyName = cdata.CompanyName,
                    MainTicker = cdata.MainTicker
                }
            };
        }

        public IList<Company> GetCompaniesSearchByCompanyId(ICollection<int> capitalIqIds)
        {
            if (capitalIqIds.Count == 0)
                return new List<Company>();

            if (capitalIqIds.Any(x => x == 0))
                throw new ArgumentException("capitalIqIds");

            var data = _corporateDataCollection
                .Find(Builders<CorporateData>.Filter.In(c => c.CapitalIqId, capitalIqIds))
                .Project<CorporateData>(Builders<CorporateData>.Projection
                    .Include(x => x.CapitalIqId)
                    .Include(x => x.CompanyName)
                    .Include(x => x.MainTicker))
                .ToList();

            if (!data.Any())
            {
                throw new CompanyMatchNotFoundException();
            }

            return data
                .Select(d => new Company
                {
                    CompanyId = d.CapitalIqId,
                    CompanyName = d.CompanyName,
                    MainTicker = d.MainTicker
                })
                .ToList();

        }
    }

    public static class NpgsqlReaderExtension
    {
        public static string GetStringValueOrDefault(this NpgsqlDataReader reader, int ordinal)
        {
            if (reader.IsDBNull(ordinal))
                return string.Empty;

            return reader.GetString(ordinal);
        }
    }
}
